<?php
namespace App\Admin\Controllers\Consultation;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Encore\Admin\Layout\Content;
use Encore\Admin\Facades\Admin;
use Illuminate\Http\Request;
use App\Admin\Controllers\Common\Common;
use Chumper\Zipper\Zipper;
class ConsultationListController extends Controller {

	public function __construct() {

	}
	/**
	 * [咨询列表]
	 */
	public function index(Content $content,Request $req) {
		$admins = DB::table("admin_users")->where('dep',5)->get(['username','ch_name','en_name']);
		return view("admin.consultation.consultation_list.index",[
			'Req' => [
				'type' => $req['type'],
				'status' => $req['status']
			],
			'Admins' => $admins
		]);
	}
	/**
	 * [咨询列表数据]
	 */
	public function getData(Request $request) {
		try {
			$params = $request->all();
			$offset = ($params['page']['pageNumber'] - 1) * $params['page']['pageSize'];
			$data = [];
			$data['total'] = $this->mkQuerySQL($params)->count();
			$sortName = $this->mkSortName($params);
			$data['rows'] = $this->mkQuerySQL($params)
				->orderBy($sortName,$params['sort']['orderBy'])
				->limit($params['page']['pageSize'])->offset($offset)->get([
					'list.*','st.*','list.step as list_step','st.step as st_step',
					'list.status as list_status','st.status as st_status',
					'list.id as list_id','st.id as st_id','st.name as st_name',
					'list.status as list_status','st.status as st_status',
					DB::raw('CEIL(DATEDIFF(NOW(),st.birthday)/365) as age'),
					'p.id as p_id','p.name as p_name','p.id_card','pts.relation'
				]);
			return ['code'=>1,'msg'=>"数据获取成功","data"=>$data];
		} catch (\Exception $e) {
			return ['code'=>0,'msg'=>"failed:".$e->getMessage(),"data"=>""];
		}
	}
	private function mkQuerySQL($params) {
		$db = DB::table("consultation_list as list")
			->leftJoin("consultation_st as st",'list.st_id','=','st.id')
			->leftJoin("consultation_parent as p",'st.primary_parent_id','=','p.id')
			->leftJoin("consultation_parent_to_st as pts",function ($join) {
            	$join->on('st.id','=','pts.st_id')->on('p.id','=','pts.p_id');
        	});
		// where list
		if($params['ask_at']){
			$db->where('list.ask_at',$params['ask_at']);
		}
		if($params['revisit_at']){
			$db->where('list.revisit_at',$params['revisit_at']);
		}
		if($params['step']){
			$db->where('list.step',$params['step']);
		}
		if($params['admin_name']){
			$db->where('list.admin_name',$params['admin_name']);
		}
		if($params['list_type']){
			$db->where('list.type',(int)$params['list_type']);
		}
		if($params['status']) {
			$db->where('list.status',(int)$params['status']);
		}
		// where st
		if($params['enroll_at']){
			$db->where('st.enroll_at',$params['enroll_at']);
		}
		if($params['st_name']){
			$db->where('st.name',"like","%".$params['st_name']."%");
		}
		if($params['phone']){
			$db->where('st.primary_phone',"like","%".$params['phone']."%");
		}
		if($params['parent_name']){
			$db->where('st.primary_parent_name',"like","%".$params['parent_name']."%");
		}
		return $db;
	}
	private function mkSortName($params) {
		switch ($params['sort']['sortBy']) {
			case 'list_id':
				$sortName = 'list.id';
				break;
			case 'enroll_at':
				$sortName = 'st.enroll_at';
				break;
			default:
				$sortName = 'list.'.$params['sort']['sortBy'];
				break;
		}
		return $sortName;
	}

	/**
	 * [数据导出]
	 */
	public function exportData(Request $request,Zipper $zipper) {
		try {
			$limit = 500;
			$type = $request['export_type'];
			$db = DB::table("consultation_list as list")
				->leftJoin("consultation_st as st","list.st_id","=","st.id")
				->where("list.type","=",(int)$request['list_type']);
			$opt = [
				"header" => [
					'序号','学生姓名','年龄','性别','主要家长','主要联系方式',
					'登记人','阶段','状态','入学时间','最新咨询时间','回访时间','备注'
				],
				'sheetname' => "sheet0",
				"save_path" => public_path()."/excel/",
				"file_name" => "consultation_list_".Admin::user()->id."_".date("ymdHis"),
			];
			if($type != "all"){
				$db->whereIn("list.id",explode(",",$request['ids']));
				$status = "done";
			}else{
				$offset = $request['offset'];
				$count = DB::table("consultation_list")->where("type","=",(int)$request['list_type'])->count();
				$status = ($offset+1)*$limit < $count ? "doing" : "done";
				$db->limit($limit)->offset($request['offset'] * $limit);
				$opt['file_name'] = "consultation_list_all_".Admin::user()->id."_".$offset;
			}
			$data = json_decode(json_encode($db->get([
				'list.id','st.name',
				DB::raw('CEIL(DATEDIFF(NOW(),st.birthday)/365) as age'),
				'st.sex','st.primary_parent_name','st.primary_phone',
				DB::raw('CONCAT(st.primary_phone,"\t") as primary_phone'),
				'list.admin_name','list.step','list.status',
				'st.enroll_at','list.ask_at','list.revisit_at','list.remark'
			])),true);
			// 写入excel
			$url = Common::exportToExcel($data,$opt);
			// zip压缩
			if( $type == "all" && $status == "done" && $offset != 0) {
				$file_arr = [];
				for($i=0;$i<=$offset;$i++) {
					$file_arr[] = $opt['save_path']."consultation_list_all_".Admin::user()->id."_".$i.".xls";
				}
				$zip_name = "consultation_list_all_".Admin::user()->id.".zip";
				$zip_path = $opt['save_path'].$zip_name;
				if(is_file($zip_path)){
					unlink($zip_path);
				}
				$zipper->make($zip_path)->add($file_arr)->close();
				$url = $zip_name;
			}
			$return = [
				'status' => $status,'fileName' => $url
			];
			return ['code'=>1,'msg'=>'success','data'=>$return];
		} catch (\Exception $e) {
			return ['code'=>0,'msg'=>"faild:".$e->getMessage(),"data"=>""];
		}
	}
}
